package com.vecspace.dicttable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

/**
 * @author: wanghua
 */

public class CodeDao {

	private DataSource dataSource; 
	
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public CodeTable fillDMTable(String tableName, String codeField, String nameField, 
			String parentField, String orderByField) throws Exception{
		
		 
		if(codeField == null || codeField.length()==0) {
			codeField = "CODE";
		}
		if(nameField == null || nameField.length()==0) {
			nameField = "NAME";		
		}
		 
		String sql = "SELECT "+codeField+","+nameField;
		if(parentField != null && parentField.length()>0){
			sql = sql + " ,"+parentField;
		}else{
			sql = sql + " ,"+codeField;
		}
		if(orderByField != null && orderByField.length()>0){
			sql = sql + " ,"+orderByField;
		}else{
			sql = sql + " ,"+codeField;
		}
		
		sql = sql + " FROM "+tableName;				
		if(orderByField != null && orderByField.length()>0){
			sql = sql + " ORDER BY "+orderByField;
		}else{
			sql = sql + " ORDER BY "+codeField;
		}
		
		List<Object[]> rows = getRecordListBySql(sql,null);
		if(rows.size()>5000) {
			throw new Exception("需要缓存的记录数太多，似乎对一个非字典表进行了缓存：" + tableName);
		}
		CodeTable dmtable = new CodeTable(tableName);
		dmtable.setCodeField(codeField);
		dmtable.setNameField(nameField);
		dmtable.setOrderByField(orderByField);
		if(rows != null && rows.size()>0){
			int rowCount = rows.size();
			for(int i=0;i<rowCount;++i){
				try{
					Object[] row = (Object[])rows.get(i);
					String code = row[0].toString();
					String name = row[1]==null?null:row[1].toString();
					CodeItem item = new CodeItem(code,name);
					try{ 
						String parentCode = row[2].toString();
						item.setParentCode(parentCode);						
					}catch(Exception ex){
						
					}
					try{
						Object ordObj = row[3];
						if(ordObj != null && ordObj.toString().length()>0){
							long ordnum = Long.valueOf(ordObj.toString());
							item.setOrdnum(ordnum);
						}						
					}catch(Exception ex){
						
					}
					dmtable.addDMItem(item);
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		return dmtable;
	}
	
	public CodeTable fillDMTableFromDictAll(String codeType, String tableName,String codeTypeField, String codeField, String nameField, String parentField, String orderByField) throws Exception{
		
		if(codeTypeField == null || codeTypeField.length()==0) {
			codeTypeField = "CODE_TYPE";
		}
		if(codeField == null || codeField.length()==0) {
			codeField = "CODE";
		}
		if(nameField == null || nameField.length()==0) {
			nameField = "NAME";		
		}

		String sql = "SELECT "+codeTypeField+","+codeField+","+nameField;
		sql = sql + " , STATUS";
		if(parentField != null && parentField.length()>0){
			sql = sql + " ,"+parentField;
		}else{
			sql = sql + " ,"+codeField;
		}
		if(orderByField != null && orderByField.length()>0){
			sql = sql + " ,"+orderByField;
		}else{
			sql = sql + " ,"+codeField;
		} 
		sql = sql + " FROM "+tableName;		
		sql = sql + " WHERE "+codeTypeField+"=?";
		if(orderByField != null && orderByField.length()>0){
			sql = sql + " ORDER BY "+orderByField;
		}else{
			sql = sql + " ORDER BY "+codeField;
		}
		
		List<Object[]> rows = getRecordListBySql(sql,new String[]{codeType});
		if(rows.size()>5000) {
			throw new Exception("需要缓存的记录数太多，似乎对一个非字典表进行了缓存：" + tableName);
		}
		CodeTable dmtable = new CodeTable(tableName);
		dmtable.setCodeField(codeField);
		dmtable.setNameField(nameField);
		dmtable.setOrderByField(orderByField);
		if(rows != null && rows.size()>0){
			int rowCount = rows.size();
			for(int i=0;i<rowCount;++i){
				try{
					Object[] row = (Object[])rows.get(i);
					String code = row[1].toString();
					String name = row[2]==null?null:row[2].toString();
					CodeItem item = new CodeItem(code,name);
					try{ 
						int status = Integer.valueOf(row[3].toString());
						item.setStatus(status);			
					}catch(Exception ex){
						
					}
					try{ 
						String parentCode = row[4].toString();
						item.setParentCode(parentCode);						
					}catch(Exception ex){
						
					}
					try{
						if(row.length>=6){
							long ordnum = Long.valueOf(row[5].toString());
							item.setOrdnum(ordnum);
						}
					}catch(Exception ex){
						
					} 
					dmtable.addDMItem(item);
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		return dmtable;
	}

	private List<Object[]> getRecordListBySql(String sql, String[] params) throws SQLException{
		Connection conn = dataSource.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Object[]> list = new ArrayList<>();
//		System.out.println("sql-----"+sql);
		try{
			stmt = conn.prepareStatement(sql);
			if(params != null && params.length>0){
				for(int i=0;i<params.length;++i){
					stmt.setString(i+1, params[i]);
				}
			}
			rs = stmt.executeQuery();
			ResultSetMetaData rsmeta = rs.getMetaData();
			while (rs.next()) {
				Object[] objs = new Object[rsmeta.getColumnCount()];
				for (int i = 0; i < objs.length; i++) {
					objs[i] = rs.getObject(i + 1);
				}				
				list.add(objs);
			}
		}finally{
			if(rs != null) {
				rs.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(conn != null) {
				conn.close();
			}
		}
		return list;
	}

	public String getCodeName(String tableName, String codeField, String nameField, String code) throws SQLException{
		String sql = "select " + nameField +" from " + tableName + " where " + codeField + "=?";
		Connection conn = dataSource.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String result = code;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, code);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				result = rs.getString(1);
			}
		}finally{
			if(rs != null) {
				rs.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(conn != null) {
				conn.close();
			}
		}
		return result;
	}
	
	public String getCodeNames(String tableName, String codeField, String nameField, String codes) throws SQLException{
		String sql = "select " + nameField +" from " + tableName + " where " + codeField + " in ('"+codes.replaceAll("\\|", "','")+"')";//TODO 改成占位符方式
		Connection conn = dataSource.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String result = codes;
		String names = "";
		try{
			stmt = conn.prepareStatement(sql);
		//	stmt.setString(1, codes);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				result = rs.getString(1);
				names+=result+"|";
			}
		}finally{
			if(rs != null) {
				rs.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(conn != null) {
				conn.close();
			}
		}
		if(!"".equals(names)){
			return names.substring(0, names.length()-1);
		}
		return result;
	}
}
